using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Framework.Library.Components.Security;
using Framework.Library.Components.Commands;
using Framework.Library;
using Framework.Library.Utilities.Database;
using Framework.Library.Utilities;
using Framework.Library.Components.Database;

namespace Framework.Portal.Administrator.Database
{
    public partial class TablesProperty : AuthenticatedControl
    {
        private DataTable dataColumns = new DataTable();
        _Table objTable = new _Table();
        private static ArrayList listOperation = new ArrayList();

        protected void Page_Load(object sender, EventArgs e)
        {
            if ((!CurrentUser.IsSuperAdmin && CommandController.IsSystemModule(AppEnv.CommandID())) || !CommandController.IsAvailableForUser(AppEnv.CommandID(), CurrentUser.UserID, Convert.ToInt32(AppEnv.PortalId())))
                Response.Redirect(AppEnv.ADMIN_CMD, true);
            if (!IsPostBack)
            {
                listOperation.Clear();
                //load datatable
                loadInitialData(objTable);
                //load script for data type
                loadDataTypeScript(objTable);
            }
        }

        protected void dropTables_SelectedIndexChanged(object sender, EventArgs e)
        {            
            LoadTableData(objTable);
        }

        private void LoadTableData(_Table objTable)
        {
            objTable.ID = Convert.ToInt64(dropTables.SelectedValue);
            //objTable.Owner = objTable.Name.Substring(0, objTable.Name.IndexOf("."));
            objTable.LoadProperties();

            //Load links            
            lnkTablesList.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.Table));
            lnkTableDrop.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.TableAction)) + "&id=" + objTable.ID + "&action=drop";
            lnkTableRename.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.TableAction)) + "&id=" + objTable.ID + "&action=rename";

            //load columns
            loadProperties(objTable);

            //Load dependencies
            loadDependencies(objTable);

            //Load constraints
            loadConstraints(objTable);

        }

        void Page_Init(object sender, EventArgs e)
        {
            loadDataTable();
        }

        private void loadInitialData(_Table objTable)
        {

            //loadTables
            dropTables.DataSource = objTable.GetTables();
            dropTables.DataTextField = "completename";
            dropTables.DataValueField = "id";
            dropTables.DataBind();

            ListItem myItem = dropTables.Items.FindByValue(Request.QueryString["id"].ToString());
            if (myItem != null)
                myItem.Selected = true;

            objTable.ID = Convert.ToInt64(dropTables.SelectedValue);
            objTable.LoadProperties();

            objTable.Name = objTable.Name;
            objTable.Owner = objTable.Owner;

            //Load links            
            lnkTablesList.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.Table));
            lnkTableDrop.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.TableAction)) + "&id=" + objTable.ID + "&action=drop";
            lnkTableRename.NavigateUrl = AppEnv.AdminUrlParams(CommandController.GetCommandbyID((int)PortalControls.TableAction)) + "&id=" + objTable.ID + "&action=rename";

            //load columns
            loadProperties(objTable);

            //Load dependencies
            loadDependencies(objTable);

            //Load Constraints
            loadConstraints(objTable);
        }

        private void loadDataTable()
        {
            dataColumns.Columns.Add("name", Type.GetType("System.String"));
            dataColumns.Columns.Add("datatype", Type.GetType("System.String"));
            dataColumns.Columns.Add("width", Type.GetType("System.String"));
            dataColumns.Columns.Add("scale", Type.GetType("System.String"));
            dataColumns.Columns.Add("identity", Type.GetType("System.String"));
            dataColumns.Columns.Add("seed", Type.GetType("System.String"));
            dataColumns.Columns.Add("increment", Type.GetType("System.String"));
            dataColumns.Columns.Add("default", Type.GetType("System.String"));
            dataColumns.Columns.Add("defaultbinding", Type.GetType("System.String"));
            dataColumns.Columns.Add("isnull", Type.GetType("System.String"));
            dataColumns.Columns.Add("defaultConstraint", Type.GetType("System.String"));
            dataColumns.Columns.Add("ruleBinding", Type.GetType("System.String"));
        }

        private void loadConstraints(_Table objTable)
        {
            treeConstraints.FindNode("PK").ChildNodes.Clear();
            treeConstraints.FindNode("UK").ChildNodes.Clear();
            treeConstraints.FindNode("CC").ChildNodes.Clear();
            treeConstraints.FindNode("FK").ChildNodes.Clear();

            foreach (DataRow objRow in objTable.GetConstraints().Rows)
            {
                TreeNode myNode = new TreeNode();
                myNode.Text = objRow["ConstName"].ToString();
                switch (objRow["ConstType"].ToString().Trim())
                {
                    case "PK":
                        myNode.NavigateUrl = "~/modules/Databases/primarykeys/key_properties.aspx?id=" + objRow["id"].ToString() + "&dbname=" + Request.QueryString["dbname"];
                        break;
                    case "UK":
                        myNode.NavigateUrl = "~/modules/Databases/uniquekeys/ukey_properties.aspx?id=" + objRow["id"].ToString() + "&dbname=" + Request.QueryString["dbname"];
                        break;
                    case "FK":
                        myNode.NavigateUrl = "~/modules/Databases/foreignkeys/fkey_properties.aspx?id=" + objRow["id"].ToString() + "&dbname=" + Request.QueryString["dbname"];
                        break;
                    case "CC":
                        myNode.NavigateUrl = "~/modules/Databases/checks/check_properties.aspx?id=" + objRow["id"].ToString() + "&dbname=" + Request.QueryString["dbname"];
                        break;
                    default:
                        myNode.NavigateUrl = "#";
                        break;
                }
                treeConstraints.FindNode(objRow["ConstType"].ToString().Trim()).ChildNodes.Add(myNode);
            }

            //load links
            lnkNewCC.NavigateUrl = "~/modules/Databases/checks/check_new.aspx?returnURL=" + Server.UrlEncode(Request.Url.ToString()) + "&objID=" + objTable.ID.ToString() + "&dbname=" + Request.QueryString["dbname"];
            lnkNewFK.NavigateUrl = "~/modules/Databases/foreignkeys/fkey_new.aspx?returnURL=" + Server.UrlEncode(Request.Url.ToString()) + "&objID=" + objTable.ID.ToString() + "&dbname=" + Request.QueryString["dbname"];
            lnkNewPK.NavigateUrl = "~/modules/Databases/primarykeys/key_new.aspx?returnURL=" + Server.UrlEncode(Request.Url.ToString()) + "&objID=" + objTable.ID.ToString() + "&dbname=" + Request.QueryString["dbname"];
            lnkNewUK.NavigateUrl = "~/modules/Databases/uniquekeys/ukey_new.aspx?returnURL=" + Server.UrlEncode(Request.Url.ToString()) + "&objID=" + objTable.ID.ToString() + "&dbname=" + Request.QueryString["dbname"];
        }

        private void loadDependencies(_Table objTable)
        {
            treeDependencies.FindNode("Ref/FN").ChildNodes.Clear();
            treeDependencies.FindNode("Ref/I").ChildNodes.Clear();
            treeDependencies.FindNode("Ref/P").ChildNodes.Clear();
            treeDependencies.FindNode("Ref/U").ChildNodes.Clear();
            treeDependencies.FindNode("Ref/TR").ChildNodes.Clear();
            treeDependencies.FindNode("Ref/V").ChildNodes.Clear();

            treeDependencies.FindNode("Dep/U").ChildNodes.Clear();
            treeDependencies.FindNode("Dep/D").ChildNodes.Clear();

            foreach (DataRow objRow in objTable.GetDependencies().Rows)
            {
                TreeNode myNode = new TreeNode();
                myNode.Text = objRow["name"].ToString();

                switch (objRow["type"].ToString().Trim())
                {
                    case "U":
                        myNode.NavigateUrl = "~/modules/Databases/tables/table_properties.aspx?id=" + objRow["id"].ToString();
                        break;
                    case "V":
                        myNode.NavigateUrl = "~/modules/Databases/views/view_properties.aspx?id=" + objRow["id"].ToString();
                        break;
                    case "P":
                        myNode.NavigateUrl = "~/modules/Databases/storedprocedure/proc_properties.aspx?id=" + objRow["id"].ToString();
                        break;
                    case "I":
                        myNode.NavigateUrl = "~/modules/Databases/indexes/index_properties.aspx?id=" + objRow["id"].ToString() + "&tableID=" + objTable.ID;
                        break;
                    case "TR":
                        myNode.NavigateUrl = "~/modules/Databases/triggers/trg_properties.aspx?id=" + objRow["id"].ToString() + "&tableID=" + objTable.ID;
                        break;
                    default:
                        myNode.NavigateUrl = "";
                        break;
                }
                treeDependencies.FindNode(objRow["direction"].ToString() + "/" + objRow["type"].ToString().Trim()).ChildNodes.Add(myNode);
            }
        }

        private void loadProperties(_Table objTable)
        {
            //load columns
            objTable.LoadColumns();
            foreach (_Column col in objTable.Columns)
            {
                DataRow myRowNew = dataColumns.NewRow();
                myRowNew["name"] = col.Name;
                myRowNew["datatype"] = col.Type;

                myRowNew["width"] = col.Width;
                myRowNew["scale"] = col.Scale;
                myRowNew["identity"] = col.IsIdentity.ToString();

                if (col.IsIdentity)
                {
                    myRowNew["seed"] = col.Seed.ToString();
                    myRowNew["increment"] = col.Increment.ToString();
                }
                else
                {
                    myRowNew["seed"] = "";
                    myRowNew["increment"] = "";
                }

                myRowNew["isnull"] = col.AllowNulls.ToString();

                myRowNew["default"] = col.Default;
                myRowNew["defaultbinding"] = col.DefaultBinding;
                myRowNew["defaultConstraint"] = col.DefaultConstraint;
                myRowNew["ruleBinding"] = col.RuleBinding;
                //Response.Write(myRowNew["defaultConstraint"]);
                dataColumns.Rows.Add(myRowNew);
            }

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();
        }

        private void loadDataTypeScript(_Table objTable)
        {
            //Load datatype
            dropDataType.Items.Add("");
            foreach (DataRow objRow in Info.GetDataTypes().Rows)
            {
                ListItem myItem = new ListItem();
                myItem.Value = objRow["Name"].ToString();
                myItem.Text = myItem.Value;
                dropDataType.Items.Add(myItem);
            }

            //load Defaults
            dropDefaults.Items.Add("");
            foreach (DataRow objRow in Info.GetDefaults().Rows)
            {
                ListItem myItem = new ListItem();
                myItem.Value = objRow["Name"].ToString();
                myItem.Text = myItem.Value;
                dropDefaults.Items.Add(myItem);
            }

            //Load Rules
            dropRules.Items.Add("");
            foreach (DataRow objRow in Info.GetRules().Rows)
            {
                ListItem myItem = new ListItem();
                myItem.Value = objRow["Name"].ToString();
                myItem.Text = myItem.Value;
                dropRules.Items.Add(myItem);
            }


            //load javascript
            ltlJavascriptDataType.Text = "";
            ltlJavascriptDataType.Text += "<script>\n";
            ltlJavascriptDataType.Text += "function checkDataType(strType, blnEdit){\n";
            ltlJavascriptDataType.Text += "		objWidth = document.getElementById('txtWidth');\n";
            ltlJavascriptDataType.Text += "		objScale = document.getElementById('txtScale');\n";
            ltlJavascriptDataType.Text += "		objScale.disabled = false;\n";
            ltlJavascriptDataType.Text += "		objWidth.disabled = false;\n";
            ltlJavascriptDataType.Text += "		switch(strType){\n";
            //loop on data type
            ltlJavascriptDataType.Text += "			case '':\n";
            ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
            ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
            ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
            ltlJavascriptDataType.Text += "				objScale.value = '';\n";
            ltlJavascriptDataType.Text += "				break;\n";
            foreach (DataRow objRow in Info.GetDataTypes().Rows)
            {
                ltlJavascriptDataType.Text += "			case '" + objRow["name"].ToString() + "':\n";
                switch (objRow["name"].ToString())
                {
                    case "bigint":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "binary":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = false;\n";
                        break;
                    case "bit":
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        break;
                    case "char":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = false;\n";
                        break;
                    case "datetime":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "decimal":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '0';\n";
                        break;
                    case "float":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "image":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "int":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "money":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "nchar":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        break;
                    case "ntext":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "numeric":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '0';\n";
                        break;
                    case "nvarchar":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        break;
                    case "real":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "smalldatetime":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "smallint":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "smallmoney":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "sql_variant":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "sysname":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "text":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "timestamp":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "tinyint":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "uniqueidentifier":
                        ltlJavascriptDataType.Text += "				objWidth.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        ltlJavascriptDataType.Text += "				objWidth.disabled = true;\n";
                        break;
                    case "varbinary":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        break;
                    case "varchar":
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["prec"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '';\n";
                        ltlJavascriptDataType.Text += "				objScale.disabled = true;\n";
                        break;
                    default:
                        ltlJavascriptDataType.Text += "				objWidth.value = '" + objRow["length"].ToString() + "';\n";
                        ltlJavascriptDataType.Text += "				objScale.value = '" + objRow["scale"].ToString() + "';\n";
                        break;
                }
                ltlJavascriptDataType.Text += "				break;\n";
            }
            ltlJavascriptDataType.Text += "		}\n";
            ltlJavascriptDataType.Text += "}\n";
            ltlJavascriptDataType.Text += "</script>";

            //associate javascript to drop
            dropDataType.Attributes.Add("onChange", "checkDataType(this.options[this.selectedIndex].value)");

            //identity javascript
            //checkIdentity.Attributes.Add("onClick", "checkIdentityField(this);");
        }

        protected void gridColumns_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //reload gridview item
            foreach (GridViewRow objRow in gridColumns.Rows)
            {
                DataRow myRow = dataColumns.NewRow();
                myRow["name"] = objRow.Cells[0].Text;
                myRow["datatype"] = objRow.Cells[1].Text;
                myRow["width"] = objRow.Cells[2].Text;
                myRow["scale"] = objRow.Cells[3].Text;

                myRow["identity"] = objRow.Cells[4].Text;
                myRow["seed"] = objRow.Cells[5].Text;
                myRow["increment"] = objRow.Cells[6].Text;

                myRow["default"] = objRow.Cells[7].Text;

                myRow["defaultbinding"] = objRow.Cells[8].Text;
                myRow["isnull"] = objRow.Cells[11].Text;
                myRow["defaultConstraint"] = objRow.Cells[9].Text;
                myRow["ruleBinding"] = objRow.Cells[10].Text;

                dataColumns.Rows.Add(myRow);
            }

            //remember the col deleted
            onDeleteRow(dataColumns.Rows[e.RowIndex]);

            dataColumns.Rows[e.RowIndex].Delete();

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();
        }

        protected void gridColumns_RowEditing(object sender, GridViewEditEventArgs e)
        {
            resetForm();

            btnUpdate.Visible = true;
            txtColumnName.Text = gridColumns.Rows[e.NewEditIndex].Cells[0].Text.ToString();
            Info.CheckDropList(dropDataType, gridColumns.Rows[e.NewEditIndex].Cells[1].Text.ToString());
            ltlExecuteJavascript.Text += "<script>\n checkDataType('" + gridColumns.Rows[e.NewEditIndex].Cells[1].Text.ToString() + "', true);\n</script>";
            txtWidth.Text = gridColumns.Rows[e.NewEditIndex].Cells[2].Text.ToString();
            txtScale.Text = gridColumns.Rows[e.NewEditIndex].Cells[3].Text.ToString().Replace("&nbsp;", "0");


            checkIdentity.Checked = Convert.ToBoolean(gridColumns.Rows[e.NewEditIndex].Cells[4].Text.ToString());

            if (checkIdentity.Checked)
            {
                txtSeed.Enabled = true;
                txtIncrement.Enabled = true;
                txtSeed.Text = gridColumns.Rows[e.NewEditIndex].Cells[5].Text.ToString();
                txtIncrement.Text = gridColumns.Rows[e.NewEditIndex].Cells[6].Text.ToString();
            }
            else
            {
                txtSeed.Text = "1";
                txtIncrement.Text = "1";
            }

            Info.CheckRadioList(radioNull, gridColumns.Rows[e.NewEditIndex].Cells[11].Text.ToString().ToLower());
            txtDefaultConstraint.Value = gridColumns.Rows[e.NewEditIndex].Cells[9].Text.ToString();
            txtDefault.Text = gridColumns.Rows[e.NewEditIndex].Cells[7].Text.ToString().Replace("&nbsp;", "");
            Info.CheckDropList(dropDefaults, gridColumns.Rows[e.NewEditIndex].Cells[8].Text.ToString());
            Info.CheckDropList(dropRules, gridColumns.Rows[e.NewEditIndex].Cells[10].Text.ToString());

            hideRowUpdate.Value = e.NewEditIndex.ToString();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow objRow in gridColumns.Rows)
            {
                DataRow myRow = dataColumns.NewRow();
                myRow["name"] = objRow.Cells[0].Text;
                myRow["datatype"] = objRow.Cells[1].Text;
                myRow["width"] = objRow.Cells[2].Text;
                myRow["scale"] = objRow.Cells[3].Text;
                myRow["identity"] = objRow.Cells[4].Text;
                myRow["seed"] = objRow.Cells[5].Text;
                myRow["increment"] = objRow.Cells[6].Text;
                myRow["default"] = objRow.Cells[7].Text;
                myRow["defaultbinding"] = objRow.Cells[8].Text;
                myRow["isnull"] = objRow.Cells[11].Text;
                myRow["defaultConstraint"] = objRow.Cells[9].Text;
                myRow["ruleBinding"] = objRow.Cells[10].Text;

                dataColumns.Rows.Add(myRow);

            }

            DataRow myRowNew = dataColumns.NewRow();
            myRowNew["name"] = txtColumnName.Text;
            myRowNew["datatype"] = dropDataType.SelectedValue;
            myRowNew["width"] = txtWidth.Text;
            myRowNew["scale"] = txtScale.Text;

            myRowNew["isnull"] = radioNull.SelectedValue;

            myRowNew["identity"] = checkIdentity.Checked.ToString();

            if (checkIdentity.Checked)
            {
                myRowNew["seed"] = txtSeed.Text;
                myRowNew["increment"] = txtIncrement.Text;
            }
            else
            {
                myRowNew["seed"] = "";
                myRowNew["increment"] = "";
            }

            myRowNew["default"] = txtDefault.Text;
            myRowNew["defaultbinding"] = dropDefaults.SelectedValue;
            myRowNew["ruleBinding"] = dropRules.SelectedValue;

            dataColumns.Rows.Add(myRowNew);

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();

            //remeber what column added
            onInsertRow(myRowNew);

            //reset the form
            resetForm();
        }

        private void resetForm()
        {
            txtColumnName.Text = "";
            dropDataType.SelectedIndex = 0;
            txtWidth.Text = "";
            txtScale.Text = "";
            radioNull.SelectedIndex = 0;
            checkIdentity.Checked = false;
            txtSeed.Enabled = false;
            txtIncrement.Enabled = false;
            txtDefault.Text = "";
            dropDefaults.SelectedIndex = 0;
            btnUpdate.Visible = false;
            ltlExecuteJavascript.Text = "";
            dropRules.SelectedIndex = 0;
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            //reload rows
            foreach (GridViewRow objRow in gridColumns.Rows)
            {
                DataRow myRow = dataColumns.NewRow();
                myRow["name"] = objRow.Cells[0].Text;
                myRow["datatype"] = objRow.Cells[1].Text;
                myRow["width"] = objRow.Cells[2].Text;
                myRow["scale"] = objRow.Cells[3].Text;

                myRow["identity"] = objRow.Cells[4].Text;
                myRow["seed"] = objRow.Cells[5].Text;
                myRow["increment"] = objRow.Cells[6].Text;

                myRow["default"] = objRow.Cells[7].Text;

                myRow["defaultbinding"] = objRow.Cells[8].Text;
                myRow["isnull"] = objRow.Cells[11].Text;
                myRow["defaultConstraint"] = objRow.Cells[9].Text;
                myRow["ruleBinding"] = objRow.Cells[10].Text;

                //Response.Write(objRow.Cells[10].Text);
                dataColumns.Rows.Add(myRow);
            }

            //update the selected row
            DataRow myRowNew = dataColumns.Rows[Convert.ToInt32(hideRowUpdate.Value)];
            //if the column Name is Changed, log the rename operation
            //*******************************************************
            colsEdit editCol = new colsEdit();
            editCol._oldName = myRowNew["name"].ToString();
            //*******************************************************

            myRowNew["name"] = txtColumnName.Text;
            myRowNew["datatype"] = dropDataType.SelectedValue;
            myRowNew["width"] = txtWidth.Text;
            myRowNew["scale"] = txtScale.Text;

            myRowNew["isnull"] = radioNull.SelectedValue;

            myRowNew["identity"] = checkIdentity.Checked.ToString();

            if (checkIdentity.Checked)
            {
                myRowNew["seed"] = txtSeed.Text;
                myRowNew["increment"] = txtIncrement.Text;
            }
            else
            {
                myRowNew["seed"] = "";
                myRowNew["increment"] = "";
            }

            //********************************************
            //Before modify the default i've to delete it
            //********************************************
            myRowNew["defaultConstraint"] = txtDefaultConstraint.Value;
            if (myRowNew["default"].ToString() != txtDefault.Text || myRowNew["defaultbinding"].ToString() != dropDefaults.SelectedValue)
            {
                objTable.ID = Convert.ToInt64(dropTables.SelectedValue);
                objTable.LoadProperties();

                //this is a mistery for me now
                myRowNew["default"] = myRowNew["default"].ToString().Replace("&nbsp;", "");
                myRowNew["defaultbinding"] = myRowNew["defaultbinding"].ToString().Replace("&nbsp;", "");

                //Default constraint
                if ((myRowNew["default"].ToString() != "" && txtDefault.Text != "") || (myRowNew["default"].ToString() != "" && txtDefault.Text == ""))
                {
                    //Response.Write(myRowNew["defaultConstraint"].ToString());
                    //Response.Write(myRowNew["default"].ToString());
                    objTable.DropDefaultConstraint(myRowNew["defaultConstraint"].ToString());
                    myRowNew["defaultConstraint"] = "";
                }
                //default binding
                if ((myRowNew["defaultbinding"].ToString() != "" && dropDefaults.SelectedValue != "") || (myRowNew["defaultbinding"].ToString() != "" && dropDefaults.SelectedValue == ""))
                {
                    objTable.DropDefaultBinding(editCol._oldName);
                }                
            }

            myRowNew["default"] = txtDefault.Text;
            myRowNew["defaultbinding"] = dropDefaults.SelectedValue;
            myRowNew["ruleBinding"] = dropRules.SelectedValue;


            //*****************************************
            editCol._Name = myRowNew["name"].ToString();
            editCol._Type = myRowNew["datatype"].ToString();
            editCol._Width = myRowNew["width"].ToString();
            editCol._Scale = myRowNew["scale"].ToString();
            editCol._IsIdentity = Convert.ToBoolean(myRowNew["identity"]);
            editCol._Seed = myRowNew["seed"].ToString();
            editCol._Increment = myRowNew["increment"].ToString();
            editCol._Default = myRowNew["default"].ToString();
            editCol._DefaultBinding = myRowNew["defaultbinding"].ToString();
            editCol._DefaultConstraint = myRowNew["defaultConstraint"].ToString();
            editCol._RuleBinding = myRowNew["ruleBinding"].ToString();
            editCol._AllowNulls = Convert.ToBoolean(myRowNew["isnull"]);
            editCol._operation = "UPD";

            listOperation.Add(editCol);
            //****************************************

            gridColumns.DataSource = dataColumns;
            gridColumns.DataBind();

            resetForm();
        }

        #region "Methods for commit the table changes"

        private struct colsEdit
        {
            public string _Name;
            public string _oldName;

            public string _Type;
            public string _Width;
            public string _Scale;
            public bool _IsIdentity;
            public string _Seed;
            public string _Increment;
            public string _Default;
            public string _DefaultBinding;
            public string _DefaultConstraint;
            public string _RuleBinding;
            public bool _AllowNulls;

            //operation executed on row
            //ADD, DEL, UPD
            public string _operation;
        }

        private void onDeleteRow(DataRow myRow)
        {
            colsEdit delCol = new colsEdit();

            delCol._Name = myRow["name"].ToString();
            delCol._Type = myRow["datatype"].ToString();
            delCol._Width = myRow["width"].ToString();
            delCol._Scale = myRow["scale"].ToString();
            delCol._IsIdentity = Convert.ToBoolean(myRow["identity"]);
            delCol._Seed = myRow["seed"].ToString();
            delCol._Increment = myRow["increment"].ToString();
            delCol._Default = myRow["default"].ToString();
            delCol._DefaultBinding = myRow["defaultbinding"].ToString();
            delCol._DefaultConstraint = myRow["defaultConstraint"].ToString();
            delCol._RuleBinding = myRow["ruleBinding"].ToString();
            delCol._AllowNulls = Convert.ToBoolean(myRow["isnull"]);
            delCol._operation = "DEL";

            listOperation.Add(delCol);
        }

        private void onInsertRow(DataRow myRow)
        {
            colsEdit newCol = new colsEdit();

            newCol._Name = myRow["name"].ToString();
            newCol._Type = myRow["datatype"].ToString();
            newCol._Width = myRow["width"].ToString();
            newCol._Scale = myRow["scale"].ToString();
            newCol._IsIdentity = Convert.ToBoolean(myRow["identity"]);
            newCol._Seed = myRow["seed"].ToString();
            newCol._Increment = myRow["increment"].ToString();
            newCol._Default = myRow["default"].ToString();
            newCol._DefaultBinding = myRow["defaultbinding"].ToString();
            newCol._DefaultConstraint = myRow["defaultConstraint"].ToString();
            newCol._RuleBinding = myRow["ruleBinding"].ToString();
            newCol._AllowNulls = Convert.ToBoolean(myRow["isnull"]);
            newCol._operation = "ADD";

            listOperation.Add(newCol);
        }

        #endregion

        protected void btnCommit_Click(object sender, EventArgs e)
        {
            objTable.ID = Convert.ToInt64(dropTables.SelectedValue);
            objTable.LoadProperties();
            try
            {
                foreach (colsEdit col in listOperation)
                {
                    _Column myColumn = new _Column();
                    myColumn.Name = col._Name;
                    myColumn.OldName = col._oldName;
                    myColumn.Type = col._Type;
                    myColumn.Width = col._Width.Replace("&nbsp;", "");
                    myColumn.Scale = col._Scale.Replace("&nbsp;", "");
                    myColumn.IsIdentity = col._IsIdentity;
                    myColumn.Seed = col._Seed.Replace("&nbsp;", "");
                    myColumn.Increment = col._Increment.Replace("&nbsp;", "");
                    myColumn.Default = col._Default.Replace("&nbsp;", "");
                    myColumn.DefaultBinding = col._DefaultBinding.Replace("&nbsp;", "");
                    myColumn.DefaultConstraint = col._DefaultConstraint.Replace("&nbsp;", "");
                    myColumn.RuleBinding = col._RuleBinding.Replace("&nbsp;", "");
                    myColumn.AllowNulls = col._AllowNulls;

                    objTable.AlterColumn(myColumn, col._operation);

                }
                //objTable.CloseConnection();
                listOperation.Clear();

                //objTable.Name = dropTables.SelectedValue.ToString();
                loadInitialData(objTable);
            }
            catch (Exception ex)
            {
                lblError.Visible = true;
                lblError.Text = "<p>" + ex.Message + "</p>";
            }
        }

        protected void checkIdentity_CheckedChanged(object sender, EventArgs e)
        {
            txtSeed.Enabled = checkIdentity.Checked;
            txtIncrement.Enabled = checkIdentity.Checked;
        }
    }
}